package com.socket.server.data_access_components;
import com.socket.server.business_entities.Motor;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MotorDAO {        
        //local variables
        private String strTable="";
        private String strFields="";
        private String strValues="";
        private String insertStr="";
                
        //table fields' Name of the database!
        private final String thisTable = "Xe";
        private final String xeID = "xeID";
        private final String xeName = "xeName";
        private final String xeColor = "xeColor";
        private final String xePrice = "xePrice";
        private final String xeMark = "xeMark";
        private final String xeQuantity = "xeQuantity";        

        public int deleteMotor(Motor motor) {            
            DBAccessor dba = new DBAccessor();
            if (dba.connectDB()) {
                Connection connection = dba.getConnection();    
                
                String sql = "DELETE FROM " + thisTable + " WHERE " + xeID + " = '" + motor.getXeID() + "'";
                try {
                    Statement stm = connection.createStatement();
                    int i = stm.executeUpdate(sql);                
                    return i;
                } catch(SQLException sqle) {
                    sqle.printStackTrace();
                    //return 0; //fail
                }
            }
            return 0;
        }
        
        public List selectMotors() { //select all
            DBAccessor dba = new DBAccessor();
            if (dba.connectDB()) {
                Connection connection = dba.getConnection();
                
                List list = new ArrayList();
                String sql;
                sql = "SELECT * FROM " + thisTable;
                //sql = "SELECT * FROM " + thisTable + " WHERE " + xeID + " = '" + id + "'";          
                try {
                    Statement stm = connection.createStatement();
                    ResultSet rs = stm.executeQuery(sql); //executeQuery   
                    /* loop */
                    while(rs.next())
                    {
                        Motor motor = new Motor();
                        motor.setXeID(rs.getString(xeID));
                        motor.setXeName(rs.getString(xeName));
                        motor.setXeColor(rs.getString(xeColor));
                        motor.setXePrice(rs.getDouble(xePrice));
                        motor.setxeMark(rs.getString(xeMark));
                        motor.setXeQuantity(rs.getInt(xeQuantity));
                        list.add(motor);
                    }
                }
                catch(SQLException sqle) {
                    sqle.printStackTrace();
                }
                return list.size() > 0 ? list : null;
            }
            return null;
        }
        
        public Motor selectMotorById(String id) {
            DBAccessor dba = new DBAccessor();
            if (dba.connectDB()) {
                Connection connection = dba.getConnection();
                String sql = "SELECT * FROM " + thisTable + " WHERE " + xeID + " = '" + id + "'";            
                Motor motor = new Motor();
                try {                
                    Statement stm = connection.createStatement();                
                    ResultSet rs = stm.executeQuery(sql); //executeQuery 
                    rs.next();

                    motor.setXeID(rs.getString(xeID));
                    motor.setXeName(rs.getString(xeName));
                    motor.setXeColor(rs.getString(xeColor));
                    motor.setXePrice(rs.getDouble(xePrice));
                    motor.setxeMark(rs.getString(xeMark));
                    motor.setXeQuantity(rs.getInt(xeQuantity));                
                } catch(SQLException sqle) {
                    motor.setXeID("");
                    //motor.setXeName("");
                    //motor.setXeColor("");
                    //motor.setXePrice(0);
                    //motor.setxeMark("");
                    //motor.setXeQuantity(0);                
                }            
                return motor;
            }
            return null;
        }
        
        public int updateMotor(Motor motor) {
            DBAccessor dba = new DBAccessor();
            if (dba.connectDB()) {
                Connection connection = dba.getConnection();
                try {
                    Statement stm = connection.createStatement();
                    String sql = "UPDATE " + thisTable + 
                    " SET " + xeName + " = '" + motor.getXeName() + "'" +                
                    ", " + xeColor + " = '" + motor.getXeColor() + "'" +
                    ", " + xePrice + " = " + motor.getXePrice() +
                    ", " + xeMark + " = '" + motor.getxeMark() + "'" +
                    ", " + xeQuantity + " = " + motor.getXeQuantity() +
                    " WHERE " + xeID +  " = '" + motor.getXeID() + "'";
                    stm.executeUpdate(sql); //executeUpdate
                    return 1; //success
                } catch(SQLException sqle) {
                    sqle.printStackTrace();
                    //return 0; //fail
                }
            }
            return 0;
        }
        
        public int addMotor(Motor motor) {
            DBAccessor dba = new DBAccessor();
            if (dba.connectDB()) {
                Connection connection = dba.getConnection();
                try {
                    Statement stm = connection.createStatement();
                    String sql = buildAddString(motor);
                    stm.executeUpdate(sql); //executeUpdate
                    return 1; //success
                } catch(SQLException sqle) {
                    sqle.printStackTrace();
                    //return 0; //fail
                }
            }
            return 0;
        }
        
        private String buildAddString(Motor motor)
        {
            //table
            strTable  = "INSERT INTO " + thisTable;
            //fields
            strFields = " (" + xeID + 
            "," + xeName + 
            "," + xeColor + 
            "," + xePrice + 
            "," + xeMark + 
            "," + xeQuantity + ")";           
            //values
            strValues = " VALUES ( '" + motor.getXeID() + 
            "' , '" + motor.getXeName() + 
            "' , '" + motor.getXeColor() + 
            "' ,  " + motor.getXePrice() + 
            "  , '" + motor.getxeMark() + 
            "' ,  " + motor.getXeQuantity() + " )";
            //insert query    
            insertStr = strTable + strFields + strValues;
            
            return insertStr;            
        }
        /**
         * 
         * @param sql
         * @return
         */
        public int executeUpdate(String sql) {
            DBAccessor dba = new DBAccessor();
            if (dba.connectDB()) {
                Connection connection = dba.getConnection();
                try {
                    Statement stm = connection.createStatement();
                    stm.executeUpdate(sql);
                    return 1;
                } catch(SQLException sqle) {
                    sqle.printStackTrace();
                    //return 0;
                }
            }
            return 0;
        }  
}